/*******************************************************************************
 * Copyright (c) 2010, 2017 西安秦晔信息科技有限公司
 * Licensed under the Apache License, Version 2.0 (the "License");
 *    you may not use this file except in compliance with the License.
 *    You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *    Unless required by applicable law or agreed to in writing, software
 *    distributed under the License is distributed on an "AS IS" BASIS,
 *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *    See the License for the specific language governing permissions and
 *    limitations under the License.
 *******************************************************************************/
package com.qinyeit.serviceapp.service.branchs;

import com.google.common.collect.Lists;
import com.qinyeit.serviceapp.entity.Dishes;
import com.qinyeit.serviceapp.entity.Material;
import com.qinyeit.serviceapp.exception.BusinessRunTimeException;
import com.qinyeit.serviceapp.service.materials.MaterialService;
import com.qinyeit.serviceapp.service.organization.BranchService;
import com.qinyeit.serviceapp.utils.Lang;
import com.qinyetech.springstage.core.entity.search.Searchable;
import com.qinyetech.springstage.core.lang.QyMap;
import com.qinyetech.springstage.core.lang.StringUtils;
import com.qinyetech.springstage.core.repository.jdbc.PageNamedParameterJdbcTemplate;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 单店版 获取数据
 */
@Slf4j
@Service
public class BranchDataServiceImpl {

    @Resource(name = "branchJdbcTemplate")
    private PageNamedParameterJdbcTemplate branchJdbcTemplate;
    @Autowired
    private BranchService branchService;
    @Autowired
    private MaterialService materialService;


    public List<Long> getbranchDefaultIds(String scope){
        String sql = "SELECT * from merchant_permissions where parent_id is null and scope = :scope";
        QyMap map = QyMap.map("scope",scope);
        List<Map<String,Object>> mapList = branchJdbcTemplate.queryForList(sql,map);
        if(CollectionUtils.isEmpty(mapList)){
            return null;
        }
        return mapList.stream().map(x->Long.valueOf(x.get("id").toString())).collect(Collectors.toList());
    }


    /**
     * 查询库存明细 将当前库存明细与今日进出库存明细做关联 当前原料库存为原始库存量+今日进库数量-今日出库数量
     * @param categoryIds
     * @param warehouseId
     * @param flag
     * @param searchKey
     * @param pageable
     * @return
     */
    public List<QyMap> queryStock(String categoryIds, Long warehouseId, String flag, String searchKey, Pageable pageable,Long merchantId){
        QyMap stockMap = queryStocks(warehouseId, null, categoryIds, searchKey,merchantId);
        List<QyMap> stocks = Lists.newArrayList();

        for(String item : stockMap.keySet()){
            stocks.add(stockMap.getAs(item, QyMap.class));
        }
        if(StringUtils.isNotBlank(flag)){
            if("max".equals(flag)){
                //最大库存数量
                stocks = stocks.stream().filter(x->x.getDouble("count")>x.getDouble("max_stock")).collect(Collectors.toList());
            }else if("min".equals(flag)){
                //最小库存数量
                stocks = stocks.stream().filter(x->x.getDouble("count")<x.getDouble("min_stock")).collect(Collectors.toList());
            }
        }
        return stocks;
    }

    /**
     * 获取库存信息
     * @param warehouseId
     * @param materialId
     * @param categoryIds
     * @param searchKey
     * @return
     */
    private QyMap queryStocks(Long warehouseId, Long materialId, String categoryIds, String searchKey,Long merchantId){

        QyMap stockMap = queryStockList(warehouseId, materialId, categoryIds, searchKey,merchantId);
        List<Map<String, Object>> recordList = queryStockRecord2(warehouseId, materialId, categoryIds, searchKey,merchantId);
        QyMap record ;
        QyMap stock ;
        for(Map<String, Object> item : recordList){
            record = QyMap.WRAP(item);
            String key = record.getLong("material_id")+"_"+record.getLong("warehouse_id");

            if(stockMap.has(key)){
                stock = stockMap.getAs(key, QyMap.class);
                if(record.getBoolean("cal_average") && record.getDouble("count")+stock.getDouble("count")!=0){
                    //需要计算成本均价
                    double cost = (stock.getDouble("count")*stock.getDouble("cost")+record.getDouble("amount"))/(stock.getDouble("count")+record.getDouble("count"));
                    stock.setv("cost", cost);
                }
                stock.setv("count", stock.getDouble("count")+record.getDouble("count"));
                stock.setv("amount", stock.getDouble("amount")+record.getDouble("amount"));
                stockMap.setv(key, stock);
            }else{
                //库存中不存在当前商品记录
                double cost = 0;
                if(record.getDouble("count")!=0){
                    cost = record.getDouble("amount")/record.getDouble("count");
                }
                record.setv("cost", cost);
                stockMap.setv(key, record);
            }
        }
        return stockMap;
    }


    private QyMap queryStockList(Long warehouseId, Long materialId, String categoryIds, String searchKey,Long merchantId) {
        QyMap params = QyMap.NEW();
        String sql =
                " SELECT  " +
                        " w.id AS warehouse_id, " +
                        " w.name AS warehouse_name, " +
                        " m.id, " +
                        " m.id AS material_id, " +
                        " m.no, " +
                        " m.name, " +
                        " m.goods_spec, " +
                        " m.unit, " +
                        " m.category_id," +
                        " m.state, " +
                        " c.name AS category_name, " +
                        " s.count, " +
                        " s.cost, " +
                        " s.amount, " +
                        " s.merchant_id, " +
                        " m.min_stock, " +
                        " m.max_stock, " +
                        " m.stocktake_type as take_type " +
                        " FROM stocks s  " +
                        " LEFT JOIN materials m ON s.material_id=m.id " +
                        " LEFT JOIN warehouses w ON s.warehouse_id=w.id " +
                        " LEFT JOIN material_categorys c ON m.category_id=c.id " +
                        " where s.merchant_id=:merchantId AND m.is_deleted=false ";

        params.setv("merchantId",merchantId);

        sql += this.buildQueryCnd(params, warehouseId, materialId, categoryIds, searchKey);
//        log.info("param 123 : {}",params);
        List<Map<String, Object>> mapList = branchJdbcTemplate.queryForList(sql,params);
        QyMap maps = QyMap.NEW();
        for (Map<String, Object> item : mapList) {
            maps.put(item.get("material_id") + "_" + item.get("warehouse_id"), QyMap.WRAP(item));
        }
        return maps;
    }


    private List<Map<String, Object>> queryStockRecord2(Long warehouseId, Long materialId, String categoryIds, String searchKey,Long merchantId){
        QyMap params = QyMap.NEW();
        String sql =
                " SELECT  " +
                        " w.id AS warehouse_id, " +
                        " w.name AS warehouse_name, " +
                        " m.id, " +
                        " m.id AS material_id, " +
                        " m.no, " +
                        " m.name, " +
                        " m.goods_spec, " +
                        " m.unit, " +
                        " m.state, " +
                        " m.category_id, " +
                        " c.name AS category_name, " +
                        " sio.count, " +
                        " sio.amount, " +
                        " sio.cal_average, " +
                        " sio.merchant_id, " +
                        " m.min_stock, " +
                        " m.max_stock, " +
                        " m.stocktake_type as take_type " +
                        " FROM stock_in_outs sio  " +
                        " LEFT JOIN materials m ON sio.material_id=m.id " +
                        " LEFT JOIN warehouses w ON sio.warehouse_id=w.id " +
                        " LEFT JOIN material_categorys c ON m.category_id=c.id " +
                        " WHERE sio.merchant_id=:merchantId AND m.is_deleted=false ";

        params.setv("merchantId",merchantId);

        sql += this.buildQueryCnd(params, warehouseId, materialId, categoryIds, searchKey);
//        log.info("param 456 : {}",params);
        sql += " order by sio.id asc ";

        return branchJdbcTemplate.queryForList(sql,params);
    }

    /**
     * 拼接条件
     * @param params
     * @param warehouseId
     * @param materialId
     * @param categoryIds
     * @param searchKey
     * @return
     */
    private String buildQueryCnd(QyMap params, Long warehouseId, Long materialId, String categoryIds, String searchKey){
        String sql = "";
        //仓库
        if(warehouseId!=null){
            sql += " AND w.id=:warehouseId ";
            params.setv("warehouseId",warehouseId);
        }
        if(materialId!=null){
            sql += " AND m.id=:materialId ";
            params.setv("materialId",materialId);
        }

        //类别
        if(StringUtils.isNotBlank(categoryIds) && !"0".equals(categoryIds)){
            Long [] cIds = Lang.str2LongArr(categoryIds);
            categoryIds = "0/"+categoryIds.replace(",", "/");
            sql += " and (c.id=:categoryId or c.parent_ids like :parentIds )";
            params.setv("categoryId",cIds[cIds.length-1]);
            params.setv("parentIds",categoryIds+"%");
        }

        //搜索关键字(名称编号)
        if(org.apache.commons.lang3.StringUtils.isNotBlank(searchKey)){
            sql += " and ( m.no like :name or m.name like :name or m.spell like :name) ";
            params.setv("name","%"+searchKey+"%");
        }

        return sql;
    }


    /**
     * 获取门店 仓库列表
     * @param merchantId
     * @return
     */
    public List<Map<String,Object>> getBranchWarehouses(Long merchantId){
        if(merchantId==null){
            throw new BusinessRunTimeException("参数错误",10001);
        }
        String sql = "SELECT * from warehouses where merchant_id = :merchantId and is_deleted = 0";
        QyMap map = QyMap.map("merchantId",merchantId);
        return branchJdbcTemplate.queryForList(sql,map);
    }


    /**
     * 获取单店的采购规格
     * @param specIds
     * @return
     */
    public List<Map<String,Object>>  getPurchaseSpecsforBranch(List<Long> specIds){
        String sql = "SELECT * from purchase_specs where id in (:specIds)";
        QyMap map = QyMap.map("specIds",specIds);
        List<Map<String,Object>> list = branchJdbcTemplate.queryForList(sql,map);
        return list;
    }

    /**
     * 获取单店 所有物料类别信息
     * @param branchId
     * @return
     */
    public List<Map<String,Object>> getMaterialCategorys(String branchId) {
        Long merchantId = branchService.getMerchantIdByBranchId(Long.valueOf(branchId));
        if (merchantId==null){
            throw new BusinessRunTimeException("该商户不存在",10001);
        }
        String sql = "SELECT * from material_categorys where merchant_id = :merchantId and is_deleted = 0";
        QyMap map = QyMap.map("merchantId",merchantId);
        List<Map<String,Object>> mapList = branchJdbcTemplate.queryForList(sql,map);
        return associateList(mapList,0L);
    }

    /**
     * 获取单店 总部下发的采购物料信息
     * @param searchable
     *     name_eq
     *     categoryId_eq
     * @return
     */
    public Page<Map<String,Object>> getPurchaseMaterials(Searchable searchable){
        String branchId = searchable.getValue("branchId_eq");
        if (StringUtils.isBlank(branchId)){
            throw new BusinessRunTimeException("请选择门店",10001);
        }
        Long merchantId = branchService.getMerchantIdByBranchId(Long.valueOf(branchId));
        if (merchantId==null){
            throw new BusinessRunTimeException("该商户不存在",10001);
        }
        String sql = "SELECT p.id, p.name AS purchase_name, p.price AS purchase_price, p.range1, p.unit AS purchase_unit\n" +
                "\t, p.material_id, p.purchase_new_price, m.name, m.no, m.pinyin,m.uuid\n" +
                "\t, m.goods_spec, m.spell, m.unit, m.category_id, c.name AS category_name,m.code\n" +
                "FROM purchase_specs p\n" +
                "\tLEFT JOIN materials m ON m.id = p.material_id\n" +
                "\tLEFT JOIN material_categorys c ON c.id = m.category_id\n" +
                "WHERE material_id IN (\n" +
                "\t\tSELECT id\n" +
                "\t\tFROM materials\n" +
                "\t\tWHERE uuid IS NOT NULL and uuid !='' \n" +
                "\t\t\tAND merchant_id = :merchantId\n" +
                "\t\t\tAND is_deleted = 0\n" +
                "\t)\n" +
                "\tAND m.state = 'NORMAL'";
        QyMap map = QyMap.map("merchantId",merchantId);
        String name = searchable.getValue("name_eq");
        if (StringUtils.isNotBlank(name)){
            sql += " and ( m.name like :name  or m.spell like :name or m.no like :name or m.code like :code)";
            map.setv("name","%"+name+"%");
            map.setv("code","%,"+name+"%");
        }
        String categoryId = searchable.getValue("categoryId_eq");
        if (StringUtils.isNotBlank(categoryId)){
            sql += " and (c.id=:categoryId or c.parent_ids like :parentIds)";
            map.setv("categoryId",categoryId);
            map.setv("parentIds","%/"+categoryId+"/%");
        }
//        log.info("number : {}",searchable.getPage().getPageNumber());
        Pageable pageable = PageRequest.of(searchable.getPage().getPageNumber(),searchable.getPage().getPageSize());//第1页，每页5条
        Page<Map<String,Object>> mapPage = branchJdbcTemplate.queryForList(sql,map,pageable);
        if(CollectionUtils.isNotEmpty(mapPage.getContent())){
            QyMap item = null;
            for (Map<String, Object> obj : mapPage.getContent()) {
                item = QyMap.WRAP(obj);
                if(StringUtils.isNotBlank(item.getString("code"))){
                    //取一个编码
                    if(item.getString("code").indexOf(","+name+",")!=-1){
                        obj.put("goodcode",name);
                    }else {
                        obj.put("goodcode",item.getString("code").substring(1,item.getString("code").indexOf(",",2)));
                    }
                    //将编码逗号分隔为数组
                    obj.put("goodcodes", org.apache.commons.lang3.StringUtils.split(item.getString("code"),","));
                }else {
                     obj.put("goodcode","");
                     obj.put("goodcodes",null);
                }
            }
        }

        return mapPage;

    }


    /**
     * 获取门店权限串
     * @param scope
     * @return
     */
    public List<Map<String,Object>> getBranchPermissions(String scope, List<Long> checkeds){
        String sql = "SELECT * from merchant_permissions where is_deleted = 0 and scope = :scope";
        List<Map<String,Object>> permissionList = branchJdbcTemplate.queryForList(sql, QyMap.map("scope",scope));
//        log.info("permissionList : {}",JSON.toJSONString(permissionList));
        return associateList(permissionList,-1L, checkeds);
    }


    /**
     *
     * associateList:处理关联 <br>
     *
     * @author wuqing
     * @param models
     * 返回根节点集合
     * @since JDK 1.7
     */
    private  List<Map<String,Object>> associateList( List<Map<String,Object>> models, Long id){
        List<Map<String,Object>> result= Lists.newArrayList();
        QyMap map = null;
        if(models!=null && models.size()>0){
            for (int i=0;i<models.size();i++) {
                map = QyMap.WRAP(models.get(i));
                if(id.equals(map.getLong("parent_id"))){
                    map.setv("children",associateList(models,map.getLong("id")));
                    result.add(map);
                }
            }
        }
        return result;
    }

    private  List<Map<String,Object>> associateList( List<Map<String,Object>> models, Long id, List<Long> checkeds){
        List<Map<String,Object>> result= Lists.newArrayList();
        QyMap map = null;
        if(models!=null && models.size()>0){
            for (int i=0;i<models.size();i++) {
                map = QyMap.WRAP(models.get(i));
                if(id.equals(map.getLong("parent_id"))){
                    List<Map<String, Object>> children = associateList(models, map.getLong("id"), checkeds);
//                    log.info("child :{}",children.size());
                    if(CollectionUtils.isNotEmpty(children)){
                        checkeds.remove(map.getLong("id"));
                    }
                    map.setv("children",children);
                    result.add(map);
                }
            }
        }
        return result;
    }


    /**
     * 获取分店的总部配送中心
     * @param branchId
     * @return
     */
    public Map<String,Object> getBranchSupplier(Long branchId) {
        Long merchantId = branchService.getMerchantIdByBranchId(branchId);
        if (merchantId==null){
            throw new BusinessRunTimeException("该商户不存在",10001);
        }
        String sql = "SELECT * from suppliers where merchant_id = :merchantId and is_chain = true";
        List<Map<String,Object>> list = branchJdbcTemplate.queryForList(sql,QyMap.map("merchantId",merchantId));
        if (CollectionUtils.isEmpty(list)){
            throw new BusinessRunTimeException("该门店暂无总部供应商信息",10001);
        }
        return list.get(0);

    }

    public List<Map<String, Object>> getDishesBranchs(Long id,Dishes dishes) {
        String sql = "SELECT m.name as branch_name,m.id,d.name,d.uuid from merchants m " +
                " LEFT JOIN dishess d on d.merchant_id = m.id " +
                " where d.uuid =:uuid and d.is_deleted = false";
        QyMap map = QyMap.map("uuid",dishes.getUuid());
        return branchJdbcTemplate.queryForList(sql,map);
    }

    /**
     * 逻辑删除单店菜品信息
     * @param dishes
     */
    public void deleteBranchDishesData(Dishes dishes) {
        //单店成品原料是够有库存
        String sql = "";
        if (dishes.getMaterialId()!=null){
            Material material = materialService.findById(dishes.getMaterialId());
            sql = "SELECT count(*) AS count FROM stock_in_outs s " +
                    " LEFT JOIN materials m ON m.id = s.material_id " +
                    " WHERE m.uuid = :uuid";
            QyMap map = QyMap.map("uuid",material.getUuid());
            long count = branchJdbcTemplate.queryForScalar(sql,map,Long.class);
            if(count>0){
                throw new BusinessRunTimeException("已使用，不允许删除",10001);
            }
            sql = "SELECT count(*) AS count FROM stocks s " +
                    " LEFT JOIN materials m ON m.id = s.material_id " +
                    " WHERE m.uuid = :uuid";
            count = branchJdbcTemplate.queryForScalar(sql,map,Long.class);
            if(count>0){
                throw new BusinessRunTimeException("已使用，不允许删除",10001);
            }
        }

        sql = "update dishess set is_deleted = 1 where uuid = :uuid";
        QyMap map = QyMap.map("uuid",dishes.getUuid());
        branchJdbcTemplate.update(sql,map);

        //删除做法加料规格关系
        sql = "DELETE FROM dishes_make_relations WHERE dishes_id in(SELECT id FROM dishess WHERE uuid =:uuid)";
        map = QyMap.map("uuid",dishes.getUuid());
        branchJdbcTemplate.update(sql,map);
        sql = "DELETE FROM dishes_specification_relations Where dishes_id in(SELECT id FROM dishess WHERE uuid =:uuid)";
        branchJdbcTemplate.update(sql,map);
        sql = "DELETE FROM dishes_feed_relations Where dishes_id in(SELECT id FROM dishess WHERE uuid =:uuid)";
        branchJdbcTemplate.update(sql,map);


        //删除原料信息
        if (dishes.getMaterialId()!=null) {
            Material material = materialService.findById(dishes.getMaterialId());
            sql = "update materials set is_deleted = 1 where uuid = :uuid";
            map = QyMap.map("uuid",material.getUuid());
            branchJdbcTemplate.update(sql,map);
        }
        //删除原料对应的成本卡信息
        sql = "SELECT DISTINCT id FROM material_cost_details WHERE cost_id IN(SELECT id FROM material_costs WHERE dishes_id IN( SELECT id FROM dishess WHERE uuid =:uuid))";
        map = QyMap.map("uuid",dishes.getUuid());
        List<Map<String,Object>> costList = branchJdbcTemplate.queryForList(sql,map);
        if(CollectionUtils.isNotEmpty(costList)){
            List<Long> ids = Lists.newArrayList();
            for (Map<String, Object> item : costList) {
                ids.add(Long.valueOf(item.get("id").toString()));
            }
           sql = "DELETE from material_cost_details where id in(:ids)";
           map = QyMap.map("ids",ids);
           branchJdbcTemplate.update(sql,map);
        }
        sql = "DELETE from material_costs where dishes_id IN(SELECT id FROM dishess WHERE uuid =:uuid)";
        map = QyMap.map("uuid",dishes.getUuid());
        branchJdbcTemplate.update(sql,map);


    }

    /**
     * 获取单店使用的菜品的uuid
     * @param branchId
     */
    public List<String> getUseDishesUUids(Long branchId) {
        Long merchantId = branchService.getMerchantIdByBranchId(branchId);
        String sql = "SELECT uuid from dishess where uuid is not null and merchant_id = :merchantId";
        QyMap map = QyMap.map("merchantId",merchantId);

        List<Map<String,Object>> list = branchJdbcTemplate.queryForList(sql,map);
        if (CollectionUtils.isEmpty(list)){
            return null;
        }
        List<String> stringList = Lists.newArrayList();
        for (Map<String, Object> item : list) {
            stringList.add(item.get("uuid").toString());
        }
        return stringList;
    }
}